package com.augurit.gzcgwsp.smartformCsdlwj.service.impl;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.dozer.DozerBeanMapper;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.augurit.common.util.lang.StringUtils;
import com.augurit.gzcgwsp.csshlj.entity.ShljProject;
import com.augurit.gzcgwsp.csshlj.entity.ShljqsBa;
import com.augurit.gzcgwsp.csshlj.form.ShljqsBaForm;
import com.augurit.gzcgwsp.smartformCsdlwj.dao.YwHwXmclDao;
import com.augurit.gzcgwsp.smartformCsdlwj.dao.YwHwXminfoDao;
import com.augurit.gzcgwsp.smartformCsdlwj.dao.YwHwXmryDao;
import com.augurit.gzcgwsp.smartformCsdlwj.entity.YwHwXmcl;
import com.augurit.gzcgwsp.smartformCsdlwj.entity.YwHwXminfo;
import com.augurit.gzcgwsp.smartformCsdlwj.entity.YwHwXmry;
import com.augurit.gzcgwsp.smartformCsdlwj.service.ISmartFormShljqsService;
import com.augurit.gzcgwsp.smartformCsdlwj.service.IYwHwXmclService;
import com.augurit.gzcgwsp.smartformCsdlwj.service.IYwHwXminfoService;
import com.augurit.gzcgwsp.smartformCsdlwj.service.IYwHwXmryService;
import com.augurit.gzcgwsp.smartformCsdlwj.web.form.ShljqsForm;
import com.augurit.gzcgwsp.smartformCsdlwj.web.form.YwHwXminfoForm;
import com.augurit.gzcgwsp.smartformCsdlwj.web.form.YwHwXmryForm;

/*
 * 陆域生活垃圾清扫车辆情况service实现类
 */
@Service
@Transactional
public class YwHwXminfoServiceImpl  implements IYwHwXminfoService{
	@Autowired
	private YwHwXminfoDao ywHwXminfoDao;
	@Autowired
	private IYwHwXmclService ywHwXmclService;
	@Autowired
	private IYwHwXmryService ywHwXmryService;
	@Autowired
	private YwHwXmclDao ywHwXmclDao;
	@Autowired
	private YwHwXmryDao ywHwXmryDao;
	@Autowired
	private ISmartFormShljqsService smartFormShljqsService;
	
	/**
	 * 获取form
	 */
	public YwHwXminfoForm getForm(Long Id) {
		YwHwXminfo entity=ywHwXminfoDao.get(Id);
		YwHwXminfoForm form=new DozerBeanMapper().map(entity,YwHwXminfoForm.class);
		return form;
	}

	public List<YwHwXminfoForm> getListByQyId(Long qyId) {
		List<YwHwXminfo> list=this.getListByQyid(qyId);
		List<YwHwXminfoForm> forms=new ArrayList<YwHwXminfoForm>();
		for(YwHwXminfo entity:list){
			YwHwXminfoForm form=new DozerBeanMapper().map(entity,YwHwXminfoForm.class);
			forms.add(form);
		}
		return forms;
	}

	/**
	 * 根据qyid查询数据
	 * @param QYId
	 * @return
	 */
	private List<YwHwXminfo> getListByQyid(Long qyId){
		String sql=" from "+YwHwXminfo.class.getName()+" where  qyid="+qyId;
		List<YwHwXminfo> list=this.ywHwXminfoDao.find(sql);
		return list;
	}

	
	private List<YwHwXminfo> getListByYwid(Long ywid, String templateCode){
		String sql=" from YwHwXminfo where  ywid="+ywid +" and tplcode='"+templateCode+"'";
		List<YwHwXminfo> list=this.ywHwXminfoDao.find(sql);
		return list;
	}
	
	public List<YwHwXminfoForm> getListByYwId(Long ywid, String templateCode){
		List<YwHwXminfo> list=this.getListByYwid(ywid,templateCode);
		List<YwHwXminfoForm> forms=new ArrayList<YwHwXminfoForm>();
		for(YwHwXminfo entity:list){
			YwHwXminfoForm form=new DozerBeanMapper().map(entity,YwHwXminfoForm.class);
			forms.add(form);
		}
		return forms;
	}

	public void SaveOrUpdate(YwHwXminfoForm form) {
		YwHwXminfo entity;
		if(form != null && form.getId() != null){
			entity =ywHwXminfoDao.get(form.getId());
		}else{
			entity =new YwHwXminfo();
		}
			new DozerBeanMapper().map(form,entity);
		this.ywHwXminfoDao.save(entity);
	}

	public Long SaveOrUpdate(YwHwXminfo entity) {
        Session session=this.ywHwXminfoDao.getSession();
        session.flush();
        session.clear();
        Long id=(Long) session.save(entity);
		return id;
	}

	public Long parseYwHwXminfo(HSSFSheet hssfsheet,Long qyid,Long ywid, String tplcode){
		Long xmid = null;
		try {
			int offsetRow = 1;
			String fwdw,xmmc,htmc,htbh,fwszq,frdb,htj,xmjl,lxdh ="";
			String khyh,yhzh,cwfzr,fzrsfz,cwjbr,jbrsfz,lxdh2,czdh,bgdz,cltS ="";
			HSSFRow hssfrow = hssfsheet.getRow(offsetRow);
			fwdw = getCellValue(hssfrow, 2);//服务单位
			offsetRow++;
			hssfrow = hssfsheet.getRow(offsetRow);
			xmmc = getCellValue(hssfrow, 2);//项目名称
			offsetRow++;
			hssfrow = hssfsheet.getRow(offsetRow);
			htmc = getCellValue(hssfrow, 2);//合同名称
			offsetRow++;
			hssfrow = hssfsheet.getRow(offsetRow);
			htbh = getCellValue(hssfrow, 2);//合同编号
			hssfrow = hssfsheet.getRow(offsetRow);
			fwszq = getCellValue(hssfrow, 6);//服务所在区
			offsetRow++;
			hssfrow = hssfsheet.getRow(offsetRow);
			frdb = getCellValue(hssfrow, 2);//法人代表
			hssfrow = hssfsheet.getRow(offsetRow);
			htj = getCellValue(hssfrow, 6);//合同价
			offsetRow++;
			hssfrow = hssfsheet.getRow(offsetRow);
			xmjl = getCellValue(hssfrow, 2);//项目经理
			hssfrow = hssfsheet.getRow(offsetRow);
			lxdh = getCellValue(hssfrow, 6);//联系电话
			offsetRow++;
			hssfrow = hssfsheet.getRow(offsetRow);
			khyh = getCellValue(hssfrow, 2);//开户银行
			hssfrow = hssfsheet.getRow(offsetRow);
			yhzh = getCellValue(hssfrow, 6);//银行账号
			offsetRow++;
			hssfrow = hssfsheet.getRow(offsetRow);
			cwfzr = getCellValue(hssfrow, 2);//财务负责人
			hssfrow = hssfsheet.getRow(offsetRow);
			fzrsfz = getCellValue(hssfrow, 6);//负责人身份证
			offsetRow++;
			hssfrow = hssfsheet.getRow(offsetRow);
			cwjbr = getCellValue(hssfrow, 2);//财务经办人
			hssfrow = hssfsheet.getRow(offsetRow);
			jbrsfz = getCellValue(hssfrow, 6);//经办人身份证
			offsetRow++;
			hssfrow = hssfsheet.getRow(offsetRow);
			lxdh2 = getCellValue(hssfrow, 2);//联系电话2
			hssfrow = hssfsheet.getRow(offsetRow);
			czdh = getCellValue(hssfrow, 6);//传真电话
			offsetRow++;
			hssfrow = hssfsheet.getRow(offsetRow);
			bgdz = getCellValue(hssfrow, 2);//办公地址
			offsetRow++;
			hssfrow = hssfsheet.getRow(offsetRow);
			cltS = getCellValue(hssfrow, 2);//车辆台数
			YwHwXminfo ywHwXminfo = new YwHwXminfo();
			ywHwXminfo.setBgdz(bgdz);
			ywHwXminfo.setCltS(cltS);
			ywHwXminfo.setCwfzr(cwfzr);
			ywHwXminfo.setCwjbr(cwjbr);
			ywHwXminfo.setCzdh(czdh);
			ywHwXminfo.setFrdb(frdb);
			ywHwXminfo.setFwdw(fwdw);
			ywHwXminfo.setFwszq(fwszq);
			ywHwXminfo.setFzrsfz(fzrsfz);
			ywHwXminfo.setHtbh(htbh);
			ywHwXminfo.setHtj(htj);
			ywHwXminfo.setHtmc(htmc);
			ywHwXminfo.setJbrsfz(jbrsfz);
			ywHwXminfo.setKhyh(khyh);
			ywHwXminfo.setLxdh(lxdh);
			ywHwXminfo.setLxdh2(lxdh2);
			ywHwXminfo.setXmjl(xmjl);
			ywHwXminfo.setXmmc(xmmc);
			ywHwXminfo.setYhzh(yhzh);
			ywHwXminfo.setYwid(ywid);
			ywHwXminfo.setTplcode(tplcode);
			ywHwXminfo.setQyid(qyid);
			return this.SaveOrUpdate(ywHwXminfo);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return xmid;
	}
	
	public void importExcel(InputStream inputStream,Long qyid,Long ywid, String tplcode){
		try{
			// 通过得到的文件输入流inputstream创建一个HSSFWordbook对象
			HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputStream);// 将is流实例到
			HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);// 第一个工作表
			HSSFRow hssfrow = hssfsheet.getRow(0);// 第一行
			Boolean hasdelete = false;
			for (int i = 0; i < hssfworkbook.getNumberOfSheets(); i++) {
				hssfsheet = hssfworkbook.getSheetAt(i);
				String sheetName = hssfsheet.getSheetName();
				sheetName = sheetName.substring(0, 7);
				if(sheetName.equals("项目情况登记表")){
					if(!hasdelete){
						String hql = "from YwHwXminfo t where t.ywid = '"+ywid+"' and t.tplcode= '"+tplcode+"'";
						List<YwHwXminfo> list = new ArrayList<YwHwXminfo>();
						list = this.ywHwXminfoDao.find(hql, list);
						if(list !=null){
							for(int k=0; k<list.size();k++){
								String hql2 = "from YwHwXmcl t where t.xmid = '"+list.get(k).getId()+"'";
								List<YwHwXmcl> list2 = new ArrayList<YwHwXmcl>();
								list2 = this.ywHwXmclDao.find(hql2, list2);
								if(list2!=null){
									for(int j=0; j<list2.size();j++){
										this.ywHwXmclDao.delete(list2.get(j));
									}
								}
								String hql3 = "from YwHwXmry t where t.xmid = '"+list.get(k).getId()+"'";
								List<YwHwXmry> list3 = new ArrayList<YwHwXmry>();
								list3 = this.ywHwXminfoDao.find(hql3, list3);
								if(list3!=null){
									for(int j=0; j<list3.size();j++){
										this.ywHwXmryDao.delete(list3.get(j));
									}
								}
								this.ywHwXminfoDao.delete(list.get(k));
							}
						}
						hasdelete=true;
					}
					Long xmid = this.parseYwHwXminfo(hssfsheet, qyid, ywid, tplcode);
					if(xmid!=null){
						ywHwXmclService.parseYwHwXmcl(hssfsheet, xmid);
						ywHwXmryService.parseYwHwXmry(hssfsheet, xmid);
						
					}
				}
			}
			
		}catch (Exception e) {
			// TODO: handle exception
		}
	}
	
	public List getFbXminfo(Long ywid, String templateCode){
		List list = new ArrayList();
		List<YwHwXminfoForm> xmlist = new ArrayList<YwHwXminfoForm>();
		xmlist = this.getListByYwId(ywid, templateCode);
		if(xmlist!=null){
			for(int i=0;i<xmlist.size();i++){
				Map<String, String> map =new HashMap<String, String>();
				String xmnr ="";
				YwHwXminfoForm info = xmlist.get(i);
				String clxx = ywHwXmclService.getClinfo(info.getId());
				List<YwHwXmryForm> rylist = ywHwXmryService.getListByXmId(info.getId());
				xmnr ="&nbsp;&nbsp;项目名称："+info.getXmmc()+"<br>"+"&nbsp;&nbsp;项目配置情况：<br>&nbsp;&nbsp;&nbsp;&nbsp;其中：设备配备"+clxx+"<br>&nbsp;&nbsp;&nbsp;&nbsp;人员配置";
				if(rylist==null){
					xmnr+="0人。";
				}else{
					xmnr+=rylist.size()+"人。";
				}
				map.put("xmid",info.getId().toString());
				map.put("xmnr", xmnr);
				map.put("startday", info.getStartday());
				map.put("endday", info.getEndday());
				map.put("gzsj", info.getGzsj());
				list.add(map);
			}
		}
		return list;
	}
	
	/**
	 * 保存副本内容
	 */
	public void saveByMap(Map map,List<ShljProject> list){
		this.saveBaseInfo(map);//保存副本
		String xmid1 = (String) map.get("xmid1");
		String xmid2 = (String) map.get("xmid2");
		String xmid3 = (String) map.get("xmid3");
		if(xmid1!=null && xmid1.length()>0){
			String startday1=(String) map.get("startday1");
			String endday1=(String) map.get("endday1");
			String gzsj1=(String) map.get("gzsj1");
			saveFormDate(Long.parseLong(xmid1), startday1, endday1, gzsj1);
		}
		if(xmid2!=null && xmid2.length()>0){
			String startday2=(String) map.get("startday2");
			String endday2=(String) map.get("endday2");
			String gzsj2=(String) map.get("gzsj2");
			saveFormDate(Long.parseLong(xmid2), startday2, endday2, gzsj2);
		}
		if(xmid3!=null && xmid3.length()>0){
			String startday3=(String) map.get("startday3");
			String endday3=(String) map.get("endday3");
			String gzsj3=(String) map.get("gzsj3");
			saveFormDate(Long.parseLong(xmid3), startday3, endday3, gzsj3);
		}
		String xmid4 = (String) map.get("xmid4");
		if(xmid4!=null && xmid4.length()>0){
			String startday4=(String) map.get("startday4");
			String endday4=(String) map.get("endday4");
			String gzsj4=(String) map.get("gzsj4");
			saveFormDate(Long.parseLong(xmid4), startday4, endday4, gzsj4);
		}
		String xmid5 = (String) map.get("xmid5");
		if(xmid5!=null && xmid5.length()>0){
			String startday5=(String) map.get("startday5");
			String endday5=(String) map.get("endday5");
			String gzsj5=(String) map.get("gzsj5");
			saveFormDate(Long.parseLong(xmid5), startday5, endday5, gzsj5);
		}
		String xmid6 = (String) map.get("xmid6");
		if(xmid6!=null && xmid6.length()>0){
			String startday6=(String) map.get("startday6");
			String endday6=(String) map.get("endday6");
			String gzsj6=(String) map.get("gzsj6");
			saveFormDate(Long.parseLong(xmid6), startday6, endday6, gzsj6);
		}
		if(list!=null){
			//保存项目登记的内容
			Session session=ywHwXmclDao.getSession();
			for(ShljProject project:list){
				session.saveOrUpdate(project);
			}
		}
		
	}
	
	/**
	 * 保存副本其他信息
	 * @param map
	 */
	private void saveBaseInfo(Map map){
		JSONObject jsonObject = JSONObject.fromObject(map); 
		ShljqsForm lyForm=(ShljqsForm) JSONObject.toBean(jsonObject, ShljqsForm.class);
		Long lyYwId=(Long) map.get("lyYwId");
		lyForm.setId(lyYwId);
		if(StringUtils.isNotBlank((String)map.get("isprint"))){
			//表示项目登记的副本
			Session session=ywHwXmclDao.getSession();
			ShljqsBa baentity=(ShljqsBa) session.get(ShljqsBa.class, Long.valueOf((String)map.get("id")));
			baentity.setIsprint((String)map.get("isprint"));
			session.update(baentity);
		}
		this.smartFormShljqsService.doUpdateFb(lyForm,"HW_SHLJQS_V1");	
	}
	
	
	
	
	private void saveFormDate(Long id,String startday,String endday, String gzsj){
//		YwHwXminfoForm infoForm = this.getForm(id);
//		infoForm.setStartday(startday);
//		infoForm.setEndday(endday);
//		infoForm.setGzsj(gzsj);
//		this.SaveOrUpdate(infoForm);
		//许   修改
		if(id != null){
			StringBuffer sb = new StringBuffer();
			sb.append("update YwHwXminfo xminfo set xminfo.startday='"+startday+"', xminfo.endday='"+endday+"',xminfo.gzsj='"+gzsj+"' where xminfo.id='"+id+"'");
			Query query = ywHwXminfoDao.getSession().createQuery(sb.toString());
			query.executeUpdate();
		}		
	}
	
	private String getCellValue(HSSFRow hssfrow,int col){
		String value;
		if (hssfrow.getCell((short) col) == null) {
			value = "";
		} else if (hssfrow.getCell((short) col).getCellType() == 0) {
			value = new Double(hssfrow.getCell((short) col).getNumericCellValue()).toString();
		} else {// 如果EXCEL表格中的数据类型为字符串型
			value = hssfrow.getCell((short) col).getStringCellValue().trim();
		}
		return value;
	}

	@Override
	public List getProjects(Long qyid,int startnum,int endnum,Long baid) {
		String sql="SELECT t.* FROM(SELECT row_number() OVER (order by s.id) AS NUM,s.* FROM SHLJ_PROJECT s" +
				" where qyid="+qyid +" and baid="+baid+") T where 1=1";
		if(startnum>0){
			sql+=" and NUM<="+endnum+" and NUM>="+startnum ;
		}
		List list=new ArrayList();
		List data=this.ywHwXminfoDao.queryForList(sql);
		if(data.size()>0){
			for(int k=0;k<data.size();k++){
				Map map=(Map) data.get(k);
				ShljProject entity=new ShljProject();
				if(StringUtils.isNotBlank((String)map.get("baid"))){
					entity.setBaid(Long.valueOf((String)map.get("baid")));
				}
				entity.setId(Long.valueOf((String)map.get("id")));
				entity.setGzsjday((String)map.get("gzsjday"));
				entity.setXmcontent((String)map.get("xmcontent"));
				entity.setStartday((String)map.get("startday"));
				entity.setEndday((String)map.get("endday"));
				if(StringUtils.isNotBlank((String)map.get("fid"))){
					entity.setFid(Long.valueOf((String)map.get("fid")));
				}
				entity.setColnums((String)map.get("colnums"));
				entity.setRownums((String)map.get("rownums"));
				entity.setQyid(Long.valueOf((String)map.get("qyid")));
				entity.setTemplateCode((String)map.get("template_code"));
				list.add(entity);
			}
			
		}
		
		return list;
	}

	@Override
	public ShljProject getProjectById(String id) {
	    Session session=this.ywHwXmclDao.getSession();
	    ShljProject enttiy=(ShljProject) session.get(ShljProject.class, Long.valueOf(id));
		return enttiy;
	}

	@Override
	public void saveProject(List<ShljProject> list) {
		if(list!=null){
			//保存项目登记的内容
			Session session=ywHwXmclDao.getSession();
			for(ShljProject project:list){
				session.saveOrUpdate(project);
			}
		}
		
	}

	@Override
	public ShljProject getProjectByPrintInfo(ShljqsBaForm baform,int row,int col) {
		String sql=" from ShljProject where baid="+baform.getId()+" and colnums='"+col+"'"+" and rownums='"+row+"'";
		Session session=ywHwXmclDao.getSession();
		List list=session.createQuery(sql).list();
		if(list.size()>0){
			return (ShljProject) list.get(0);
		}
		
		return null;
	}
}
